package database;

import database.common.ConnectionFactory;
import model.KostoType;
import model.Pagesa;

import java.sql.*;

/**
 * Created with IntelliJ IDEA.
 * Author: Kostika Gorica
 * User:   kosta
 * Date:   3/10/13
 * Time:   10:33 PM
 */
public class PagesaDao {

    private static final String INSERT_PAGESA =
            "INSERT INTO public.pagesa ( prodhim_id, kosto_name,vlera_totale,vlera_efektive,data_pageses,data_veprimit )" +
                    " VALUES (?,?,?,?,?,?)";
    private static final String UPDATE_PAGESA =
            "UPDATE public.pagesa SET prodhim_id=?, kosto_name=?,vlera_totale=?,vlera_efektive=?,data_pageses=?,data_veprimit=? WHERE id=? ";
    private static final String DELETE_PAGESA =
            "DELETE FROM public.pagesa WHERE id = ?";
    private static final String SELECT_SINGLE_PAGESA =
            "SELECT id, prodhim_id, kosto_name,vlera_totale,vlera_efektive,data_pageses,data_veprimit FROM public.pagesa WHERE id=?";
    private static final String SELECT_ALL_PAGESAT =
            "SELECT * from query_to_xml('SELECT * FROM public.pagesa',true,false,'')";


    public static int insert(Pagesa pagesa){
        Connection connection = null;
        PreparedStatement statement = null;
        int rows = 0;
        try {
            connection = ConnectionFactory.getConnection();
            statement = connection.prepareStatement(INSERT_PAGESA);
            statement.setInt(1, pagesa.getProdhimiID());
            statement.setString(2, pagesa.getKostoName());
            statement.setDouble(3, pagesa.getVleraTotale());
            statement.setDouble(4, pagesa.getVleraEfektive());
            statement.setTimestamp(5, new Timestamp(pagesa.getDataPageses().getTime()));
            statement.setTimestamp(6, new Timestamp(pagesa.getDataVeprimit().getTime()));
            rows = statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try{
                if (statement!=null) statement.close();
                if (connection!=null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return rows;
    }

    public static int update(Pagesa pagesa){
        Connection connection = null;
        PreparedStatement statement = null;
        int rows = 0;
        try {
            connection = ConnectionFactory.getConnection();
            statement = connection.prepareStatement(UPDATE_PAGESA);
            statement.setInt(1, pagesa.getProdhimiID());
            statement.setString(2, pagesa.getKostoName());
            statement.setDouble(3, pagesa.getVleraTotale());
            statement.setDouble(4, pagesa.getVleraEfektive());
            statement.setTimestamp(5, new Timestamp(pagesa.getDataPageses().getTime()));
            statement.setTimestamp(6, new Timestamp(pagesa.getDataVeprimit().getTime()));
            rows = statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try{
                if (statement!=null) statement.close();
                if (connection!=null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return rows;
    }

    public static int delete(int id){
        Connection connection = null;
        PreparedStatement statement = null;
        int rows = 0;
        try {
            connection = ConnectionFactory.getConnection();
            statement = connection.prepareStatement(DELETE_PAGESA);
            statement.setInt(1, id);
            rows = statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try{
                if (statement!=null) statement.close();
                if (connection!=null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return rows;
    }

    public static Pagesa selectPagesa(int id){
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        Pagesa pagesa = null;
        try {
            connection = ConnectionFactory.getConnection();
            statement = connection.prepareStatement(SELECT_SINGLE_PAGESA);
            statement.setInt(1, id);
            resultSet = statement.executeQuery();
            if (resultSet.next()){
                pagesa = new Pagesa();
                pagesa.setId(resultSet.getInt(1));
                pagesa.setProdhimiID(resultSet.getInt(2));
                pagesa.setKostoName(resultSet.getString(3));
                pagesa.setVleraTotale(resultSet.getDouble(4));
                pagesa.setVleraEfektive(resultSet.getDouble(5));
                pagesa.setDataPageses(resultSet.getTimestamp(6));
                pagesa.setDataVeprimit(resultSet.getTimestamp(7));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try{
                if (resultSet!=null) resultSet.close();
                if (statement!=null) statement.close();
                if (connection!=null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return pagesa;
    }

    public static String selectAllPagesat(){
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        String result = "";
        try {
            connection = ConnectionFactory.getConnection();
            statement = connection.prepareStatement(SELECT_ALL_PAGESAT);
            resultSet = statement.executeQuery();
            if (resultSet.next()){
                result=resultSet.getString(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try{
                if (resultSet!=null) resultSet.close();
                if (statement!=null) statement.close();
                if (connection!=null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return result;
    }

}
